IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesAll') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdCSCRDeliverablesAll
    IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesAll') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdCSCRDeliverablesAll >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdCSCRDeliverablesAll >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdCSCRDeliverablesAll
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 08/19/2008 
--
-- Description: View to Deliverable Statuses, for CSCR Reports.
-- Not limited in the list like dbo.vwInfoEdCSCRDeliverables
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 08/19/08    KC        Created
--
-- =======================================================================
CREATE VIEW dbo.vwInfoEdCSCRDeliverablesAll
AS
      
SELECT    D.prop_no,
          D.ReportId,
          D.ReportName,
          RTRIM(C1.code_desc) AS REPORTCAT,
          SUBSTRING(D.REPORTCAT,LEN('PARTNERS_P_')+1,100) AS REPORTCAT_CODE,
          D.REPORTCAT AS REPORTCAT_CODE_LONG,
          C2.code_desc AS REPORTTYPE,
          D.CompletedDate,
          CASE WHEN S.REPORTSTAT = 'PARTNERS_P_REPORTSTAT_61' AND D.CompletedDate IS NOT NULL
               -- Convert to CSCR - Completed
               THEN 'CSCR - Completed'
               ELSE RTRIM(C3.code_desc)
          END AS REPORTSTAT,
          SUBSTRING(S.REPORTSTAT,LEN('PARTNERS_P_')+1,100) AS REPORTSTAT_CODE,
          S.REPORTSTAT AS REPORTSTAT_CODE_LONG,
          S.StatusDate,
          -- For SubContracts, FolderNumber should be ReportName...
          CASE WHEN D.REPORTCAT = 'PARTNERS_P_REPORTCAT_34' 
               THEN D.ReportName + ': ' + S.Comments
               ELSE S.Comments
          END AS Comments,
          CASE WHEN D.SubmittedDate IS NOT NULL
               THEN 1
               ELSE 0
          END AS ConvertedInd,
          D.DeliverableID,
          S.ProcessedDate
FROM      dbo.InfoEdPTDeliverable D LEFT OUTER JOIN dbo.InfoEdCodetab C1 ON D.REPORTCAT = C1.codeID
               LEFT OUTER JOIN dbo.InfoEdCodetab C2 ON D.REPORTTYPE = C2.codeID
               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID
               LEFT OUTER JOIN dbo.InfoEdCodetab C3 ON S.REPORTSTAT = C3.codeID
WHERE     D.Inst_Code = 'PARTNERS'      
go
IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesAll') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdCSCRDeliverablesAll >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdCSCRDeliverablesAll >>>'
go
